package com.lan.app.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.lan.app.repository.Tpl;
import com.lan.support.page.PageUtils;
import com.lan.support.page.Paging;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.collections.CollectionUtils;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;


@Repository
public class TplDao {

    private static final String tbName = "tb_tpl";

    private static final String colSQL = "id,uid,name,tpl,strategy,state,is_del,create_time,update_time";

    private static final String simColSQL = "id,uid,name,state,is_del,create_time,update_time";

    private static final String insertSQL = "INSERT INTO " + tbName + "(uid,name,tpl,strategy,state,is_del,create_time,update_time) VALUES(:uid,:name,:tpl,:strategy,:state,:isDel,:createTime,:updateTime)";

    private static final String selectSQL = "SELECT " + colSQL + " FROM " + tbName + " WHERE id=:id";

    private static final String isDelSQL = "UPDATE " + tbName + " SET is_del=:isDel,update_time=:updateTime WHERE id=:id";

    private static final String selectIdListSQL = "SELECT " + colSQL + " FROM " + tbName + "  WHERE id IN(:itemList)";

    @Resource
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public int save(Tpl tpl) {
        SqlParameterSource ps = new BeanPropertySqlParameterSource(tpl);
        KeyHolder keyHolder = new GeneratedKeyHolder();
        namedParameterJdbcTemplate.update(insertSQL, ps, keyHolder);
        tpl.setId(keyHolder.getKey().intValue());
        return keyHolder.getKey().intValue();
    }


    public Integer modifyDel(Integer id, Integer isDel, Date curDate) {
        Map<String, Object> param = new HashMap();
        param.put("id", id);
        param.put("isDel", isDel);
        param.put("updateTime", curDate);
        return namedParameterJdbcTemplate.update(isDelSQL, param);
    }

    public int updateById(Tpl tpl) {
        StringBuilder sb = new StringBuilder("UPDATE " + tbName + " SET update_time=:updateTime");
        Map<String, Object> param = new HashMap();
        param.put("updateTime", new Date());
        if (tpl.getUid() != null) {
            sb.append(",uid=:uid");
            param.put("uid", tpl.getUid());
        }
        if (StringUtils.isNotBlank(tpl.getName())) {
            sb.append(",name=:name");
            param.put("name", tpl.getName());
        }
        if (StringUtils.isNotBlank(tpl.getTpl())) {
            sb.append(",tpl=:tpl");
            param.put("tpl", tpl.getTpl());
        }
        if (tpl.getState() != null) {
            sb.append(",state=:state");
            param.put("state", tpl.getState());
        }
        if (StringUtils.isNotBlank(tpl.getStrategy())) {
            sb.append(",strategy=:strategy");
            param.put("strategy", tpl.getStrategy());
        }
        sb.append(" WHERE id=:id");
        param.put("id", tpl.getId());
        return namedParameterJdbcTemplate.update(sb.toString(), param);
    }

    public Tpl findById(Integer id) {
        Map<String, Object> param = new HashMap();
        param.put("id", id);
        List<Tpl> list = namedParameterJdbcTemplate.query(selectSQL, param, new Mapper());
        return CollectionUtils.isEmpty(list) ? null : list.get(0);
    }


    public List<Tpl> findByIdList(List<Integer> itemList) {
        Map<String, Object> param = new HashMap<>();
        param.put("itemList", itemList);
        return namedParameterJdbcTemplate.query(selectIdListSQL, param, new Mapper());
    }

    public Integer countByPage(Tpl tpl, Paging page) {
        StringBuilder sb = new StringBuilder("SELECT COUNT(id) FROM " + tbName + " WHERE is_del=1 ");
        Map<String, Object> param = new HashMap();
        where(sb, param, tpl);
        PageUtils.page(page, param, sb, false);
        return namedParameterJdbcTemplate.queryForObject(sb.toString(), param, Integer.class);
    }

    public List<Tpl> findByPage(Tpl tpl, Paging page) {
        StringBuilder sb = new StringBuilder("SELECT " + simColSQL + " FROM " + tbName + " WHERE is_del=1 ");
        Map<String, Object> param = new HashMap();
        where(sb, param, tpl);
        PageUtils.page(page, param, sb, false);
        PageUtils.page(page, param, sb);
        return namedParameterJdbcTemplate.query(sb.toString(), param, new SimMapper());
    }

    private void where(StringBuilder sb, Map<String, Object> param, Tpl tpl) {
        if (tpl.getUid() != null) {
            param.put("uid", tpl.getUid());
            sb.append(" AND uid=:uid");
        }
        if (StringUtils.isNotBlank(tpl.getName())) {
            param.put("name", tpl.getName());
            sb.append(" AND name=:name");
        }
        if (tpl.getState() != null) {
            sb.append(" AND state=:state");
            param.put("state", tpl.getState());
        }

    }

    class SimMapper implements RowMapper<Tpl> {
        @Override
        public Tpl mapRow(ResultSet rs, int i) throws SQLException {
            Tpl item = new Tpl();
            item.setId(rs.getInt("id"));
            item.setUid(rs.getInt("uid"));
            item.setName(rs.getString("name"));
            item.setState((Integer) rs.getObject("state"));
            item.setIsDel(rs.getInt("is_del"));
            item.setCreateTime(rs.getTimestamp("create_time"));
            item.setUpdateTime(rs.getTimestamp("update_time"));
            return item;
        }
    }

    class Mapper implements RowMapper<Tpl> {
        @Override
        public Tpl mapRow(ResultSet rs, int i) throws SQLException {
            Tpl item = new Tpl();
            item.setId(rs.getInt("id"));
            item.setUid(rs.getInt("uid"));
            item.setName(rs.getString("name"));
            item.setTpl(rs.getString("tpl"));
            item.setStrategy(rs.getString("strategy"));
            item.setState((Integer) rs.getObject("state"));
            item.setIsDel(rs.getInt("is_del"));
            item.setCreateTime(rs.getTimestamp("create_time"));
            item.setUpdateTime(rs.getTimestamp("update_time"));
            return item;
        }
    }

}
